<?php

if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class UsersModel extends BaseModel {

	public function UsersModel() {
		parent::BaseModel();
		$this->setModel('users');
	}

	public function getUserActivity() {
		$sql = "SELECT S.* ".
				" FROM ci_sessions S ".
				" ORDER BY last_activity DESC"; 
		
		$rs = $this->db->query($sql)->result_array();

		foreach ($rs as $key => $row) {
			if (strlen($row['session_data'])) {
				$sessionData = @unserialize($row['session_data']);
				$rs[$key]['sessionData'] = $sessionData;
			} else $rs[$key]['sessionData'] = false;
			
		}
		
		return ($rs);
	}
	
	public function getUserWorkDone($userid, $dateafter = false) {
		$sql = "SELECT U.id, U.firstname, U.lastname, T.hoursworked, T.dateapplicable, J.job_title, C.company_name
				FROM users U LEFT JOIN timesheet T ON (U.id = T.creatorid)
					LEFT JOIN job J ON (T.jobid = J.id)
					LEFT JOIN company C ON (T.companyid = C.id)
				WHERE 1 = 1 
				AND U.id = ".intval($userid)." ";
				
		if ($dateafter) $sql .= " AND T.dateapplicable >= ".$this->db->escape($dateafter);

		$sql .= "ORDER BY T.dateapplicable DESC";

		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;	
	}
	
	public function getUserWorkHours($userid = 0, $dateafter = false) {
		$sql = "SELECT U.id, U.firstname, U.lastname, SUM(T.hoursworked) as hours_worked, T.dateapplicable
				FROM users U LEFT JOIN timesheet T ON (U.id = T.creatorid)
				WHERE 1 = 1 ";
		
		if ($userid) $sql .= " AND U.id = ".intval($userid)." ";
		if ($dateafter) $sql .= " AND dateapplicable >= ".$this->db->escape($dateafter);
				
		$sql .= "GROUP BY U.id, T.dateapplicable";
		
		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getRecentReferrers() {
		$sql = "SELECT S.referrer, S.last_activity 
				FROM ci_sessions S 
				WHERE S.referrer != '0'
					AND S.referrer != ''
					AND S.referrer IS NOT NULL
				GROUP BY S.referrer
				ORDER BY S.last_activity DESC
				LIMIT 5"; 
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}

}
/*
	ALTER TABLE `users` ADD `creatorid` int(11) DEFAULT NULL ;
	ALTER TABLE `users` ADD `updaterid` int(11) DEFAULT NULL ;
	ALTER TABLE `users` ADD `resetbee` varchar(50) DEFAULT NULL ;
	*/
?>